package bean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class BoardDTO {
	
	private BoardVO vo = null;
	private Connection conn = null;
	
	public BoardDTO() {
				
		conn = new MyOracle().getConn();
	}
	
	public boolean login(){
		
		boolean flag = false;
		
		try {
			String sql = "select mid, pwd from board where mid=? and pwd=?";
			
			PreparedStatement psmt = conn.prepareStatement(sql);
			psmt.setString(1, vo.getMid());
			psmt.setString(2, vo.getPwd());
			
			ResultSet rs = psmt.executeQuery();
			if(rs.next()){
				flag = true;
			}
			
			psmt.close();
			conn.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return flag;
	}
	
	//페이지에 관련된 변수
	int listSize = 30;  //페이지당 표시되는 행수
	int blockSize = 5; //한블럭당 표시되는 페이지 수
	int totSize = 0;   //전체 게시물의 갯수
	int totPage = 0;   //전체 페이지 수
	int totBlock = 0;  //전체 블럭 수
	int nowPage = 0;   //현재 페이지
	int nowBlock = 0;  //현재 블럭
	int startNo = 0;   //리스트 목록의 시작 위치
	int endNo = 0;     //리스트 목록의 끝 위치
	int startPage = 0; //현재 블럭에서 표시되는 시작 블럭 번호
	int endPage = 0;   //현재 블럭에서 표시되는 끝 블럭 번호
	
	//페이지 관련 연산
	public void page(){
		
		String sql = "select count(*) cnt from board11 where no like ?";
		
		try {
			PreparedStatement psmt = conn.prepareStatement(sql);
			psmt.setString(1, "%"+vo.getFind()+"%");
			
			ResultSet rs = psmt.executeQuery();
			
			while(rs.next()){
				
				totSize = rs.getInt("cnt");
				//전체 페이지 수
				totPage = (int)(Math.ceil(totSize*1.0 / listSize));
				//전체 블럭 수
				totBlock = (int)(Math.ceil(totPage*1.0 / blockSize));
				//현재 블럭
				nowBlock = (int)Math.ceil(nowPage*1.0 / blockSize);
				//페이지의 시작과 끝 번호
				endPage = nowBlock * blockSize;
				startPage = endPage - blockSize +1;
				//게시물의 시작 번호와 끝 번호
				endNo = nowPage * listSize;
				startNo = endNo - listSize +1;
				//endPage, endNo 보정값
				if(endPage>totPage){
					endPage = totPage;
				}
				if(endNo>totSize){
					endNo = totSize;
				}
			}
			
			System.out.println(("totSize= "+totSize));
			System.out.println(("totPage= "+totPage));
			System.out.println(("totBlock= "+totBlock));
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
		
	public ArrayList<BoardVO> select(){
		
		page();
		
		ArrayList<BoardVO> list = new ArrayList<BoardVO>();
		String sql = "select * from (select rownum rn, c.* from "
				+ "(select * from board11 where no like ? order by grp desc,seq)c)"
				+ "b where b.rn between ? and ?";
//		String sql = "select case when lvl=0 then no when lvl>0 then null end no, rpad('└', lvl, '─')||title title from board11 order by grp desc, seq;";
//		String sql ="select * from board11 where no =?";
		try {
			PreparedStatement psmt = conn.prepareStatement(sql);
			psmt.setString(1, "%"+vo.getFind()+"%");
//			psmt.setString(2, "%"+vo.getFind()+"%");
			psmt.setInt(2, startNo);
			psmt.setInt(3, endNo);
			ResultSet rs = psmt.executeQuery();
			
			while(rs.next()){
				
				BoardVO v = new BoardVO();
				
				v.setTitle(rs.getString("title"));
				v.setNo(rs.getInt("no"));
				v.setGrp(rs.getInt("grp"));
				v.setSeq(rs.getInt("seq"));
				v.setLvl(rs.getInt("lvl"));
//				v.setwDate(rs.getString("wdate"));
//				v.setIndent(rs.getString("indent"));
				//v.setPwd(rs.getString("pwd"));
				//v.setTag(rs.getString("tag"));
				//v.setmDate(rs.getString("mdate"));
				//v.setAttfile(rs.getString("attfile"));
				
				
				list.add(v);
			}
			
			psmt.close();
			rs.close();
			conn.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return list;
	}
	
public BoardVO selectOne(){
		
	BoardVO v = new BoardVO();
		
		String sql = "select * from board11 where no =?";
		
		try {
			PreparedStatement psmt = conn.prepareStatement(sql);
			System.out.println(vo.getNo());
			psmt.setInt(1, vo.getNo());
			ResultSet rs = psmt.executeQuery();
			
			if(rs.next()){
				v.setNo(rs.getInt("no"));
				v.setGrp(rs.getInt("grp"));
				v.setTitle(rs.getString("title"));
				v.setSeq(rs.getInt("seq"));
				v.setLvl(rs.getInt("lvl"));
			}
			
			rs.close();
			psmt.close();
			conn.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return v;
	}
	
	public boolean insert(){
		
		boolean flag = false;
		
		try {
			String sql = "insert into board11(no, title,grp, seq, lvl)values(board_no_seq.nextval, ?, board_no_seq.currval, 1, 0)";
			
			PreparedStatement psmt = conn.prepareStatement(sql);
			psmt.setString(1, vo.getTitle());
		
			
			int cnt = psmt.executeUpdate();
			if(cnt>0){
				flag = true;
			}
		
			psmt.close();
			conn.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
	
	public boolean repInsert(){
		
		boolean flag = false;
		
		try {
			String sql = "insert into board11(no,title,grp,seq,lvl) values(board_no_seq.nextval, ?,?, 2, 1)";
			
			PreparedStatement psmt = conn.prepareStatement(sql);
//			psmt.setInt(1, vo.getNo());
			psmt.setString(1, vo.getTitle());
			psmt.setInt(2, vo.getGrp());
			
			int cnt = psmt.executeUpdate();
			if(cnt>0){
				flag = true;
			}
			psmt.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
	
	public BoardVO getVo() {
		return vo;
	}
	
	public void setVo(BoardVO vo) {
		this.vo = vo;
		this.nowPage = vo.getNowPage();
		this.nowBlock = vo.getNowBlock();
	}

	public int getListSize() {
		return listSize;
	}

	public void setListSize(int listSize) {
		this.listSize = listSize;
	}

	public int getBlockSize() {
		return blockSize;
	}

	public void setBlockSize(int blockSize) {
		this.blockSize = blockSize;
	}

	public int getTotSize() {
		return totSize;
	}

	public void setTotSize(int totSize) {
		this.totSize = totSize;
	}

	public int getTotPage() {
		return totPage;
	}

	public void setTotPage(int totPage) {
		this.totPage = totPage;
	}

	public int getTotBlock() {
		return totBlock;
	}

	public void setTotBlock(int totBlock) {
		this.totBlock = totBlock;
	}

	public int getNowPage() {
		return nowPage;
	}

	public void setNowPage(int nowPage) {
		this.nowPage = nowPage;
	}

	public int getNowBlock() {
		return nowBlock;
	}

	public void setNowBlock(int nowBlock) {
		this.nowBlock = nowBlock;
	}

	public int getStartNo() {
		return startNo;
	}

	public void setStartNo(int startNo) {
		this.startNo = startNo;
	}

	public int getEndNo() {
		return endNo;
	}

	public void setEndNo(int endNo) {
		this.endNo = endNo;
	}

	public int getStartPage() {
		return startPage;
	}

	public void setStartPage(int startPage) {
		this.startPage = startPage;
	}

	public int getEndPage() {
		return endPage;
	}

	public void setEndPage(int endPage) {
		this.endPage = endPage;
	}
}
